
//---------------------------------------------------------START------------------------------------------------------------

If exists (select 1 from sysobjects where name ='tr_imsgo_notification_patient_checkin' and Type='TR') then
 DROP TRIGGER "tr_imsgo_notification_patient_checkin";
ELSE
CREATE TRIGGER "tr_imsgo_notification_patient_checkin" after insert,delete,update order 2 on
"DBA"."patient_checkin"
referencing old as "old_name" new as "new_name"
for each row
begin
  if inserting then
    //Call the SP to insert the new row.
    call "sp_imsgo_notification"()
  elseif updating then
    //Update the fields
    update "imsgo_notification_master" set "notification_sent" = 0,
      "action_type" = 2,
      "action_value" = 'update'
      where "tran_id" = "new_name"."tran_id"
  elseif deleting then
    //Update the fields
    update "imsgo_notification_master" set "notification_sent" = 0,
      "action_type" = 3,
      "action_value" = 'delete'
      where "tran_id" = "new_name"."tran_id"
  end if
end
End If;

//------------------------------------------------------------END---------------------------------------------------------



//---------------------------------------------------------START------------------------------------------------------------

If exists (select 1 from sysobjects where name ='tr_imsgo_notification_progress_main' and Type='TR') then
 DROP TRIGGER "tr_imsgo_notification_progress_main";
ELSE
CREATE TRIGGER "tr_imsgo_notification_progress_main" after insert,update order 1 on
"DBA"."progress_main"
for each row
begin
  call "sp_imsgo_notification"()
end
End If;



//------------------------------------------------------------END---------------------------------------------------------




//---------------------------------------------------------START------------------------------------------------------------

If exists (select 1 from sysobjects where name ='tr_imsgo_notification_schedule_detail' and Type='TR') then
 DROP TRIGGER "tr_imsgo_notification_schedule_detail";
ELSE
CREATE TRIGGER "tr_imsgo_notification_schedule_detail" after insert,delete,update order 3 on
"DBA"."schedule_detail"
referencing old as "old_name" new as "new_name"
for each row
begin
  if inserting then
    //Call the SP to insert the new row.
    call "sp_imsgo_notification"()
  elseif updating then
    //Update the fields
    update "imsgo_notification_master" set "notification_sent" = 0,
      "action_type" = 2,
      "action_value" = 'update'
      where "tran_id" = "new_name"."tran_id"
  elseif deleting then
    //Update the fields
    update "imsgo_notification_master" set "notification_sent" = 0,
      "action_type" = 3,
      "action_value" = 'delete'
      where "tran_id" = "new_name"."tran_id"
  end if
end
End If;



//------------------------------------------------------------END---------------------------------------------------------




//---------------------------------------------------------START------------------------------------------------------------

If exists (select 1 from sysobjects where name ='sp_imsgo_notification' and Type='P') then
  Drop PROCEDURE sp_imsgo_notification;
ELSE

CREATE PROCEDURE "DBA"."sp_imsgo_notification"()
//  ----  END : Error trace and Log update  ----
//  ******* START : Statement [Ver:, VerDBId:, DB change date: ] *******
//---------------------@~@~r~n$~$------------------------
///////////////////////////////////////////////////////////////////////////////////
// Copyright © Meditab Software Inc. 2014
//
// Event/Function:
//
// Arguments: None
//
// Returns: None
//
// Description:
//
// Version History
//     1.0          Nov 4, 2015          New Func          Udit Jindal           Purpose: Sending push notification in IMSGo
///////////////////////////////////////////////////////////////////////////////////
begin
  /* --------------Work Flow---------------------------
* Delete All records for created_date < today()
* Check schedule_detail table.
* For tran_date = today(), take all appointments,
* Get doctor_id, tran_id.
* Insert into imsgo_notification_master if not present.
* Now insert records into imsgo_pending_notifications. // Moving to new procedure.
* Update all records in imsgo_notification_master.notification_sent = 1. // Moving to new procedure.
* This is done so that deadlock can be avaoided on imsgo_pending_notifications
*/
  //DECLARE "" long varchar; // Store sql string
  /* Deleting all the records that are not of current date */
  delete from "imsgo_notification_master"
    where "created_date" < "today"();
  if sqlcode < 0 then
    rollback work;
    return '-1'
  end if;
  // ELSE CONTINUE
  /* Updating notification table */
  /* Getting records from schedule_detail */
  insert into "imsgo_notification_master"( "tran_id","tran_id_type","doctor_id","notification_sent","action_type","action_value" ) on existing skip(
    select "tran_id",1,"user_name",0,1,'insert'
      from "schedule_detail"
      where "tran_date" = "today"() union
    select "patient_checkin"."tran_id",2,"patient_checkin"."doctor_id",0,1,'insert'
      from "patient_checkin"
        left outer join "progress_main" on "progress_main"."id" = "patient_checkin"."pn_id"
      where "patient_checkin"."checkin_date" = "today"()
      and "REPLACE"("progress_main"."visit_status",',','') = 14
      and "patient_checkin"."status" = 'N'); /* Logic for patient waiting for provider is here. */
  if sqlcode < 0 then
    //rollback work; //remove to use inside trigger
    return '-2'
  end if;
  //ELSE CONTINUE
  /* Now insert records into imsgo_pending_notifications */
  /*insert into "imsgo_pending_notifications"( "device_push_id","device_type","doctor_id","value","notification_type" ) on existing skip(
select "dm"."device_push_id","dm"."device_type","nm"."doctor_id","count"(1),"nm"."tran_id_type"
from "imsgo_notification_master" as "nm"
left outer join "emp_master" on "emp_master"."empid" = "nm"."doctor_id"
left outer join "security_users" as "su" on "emp_master"."user_name" = "su"."user_name"
left outer join "imsgo_device_mst" as "dm" on "dm"."device_no" = "su"."imsgo_device_no"
where "nm"."notification_sent" = 0
group by "nm"."doctor_id","dm"."device_push_id","dm"."device_type","nm"."tran_id_type");
if sqlcode < 0 then
rollback work;
return '-3'
end if;*/
  //ELSE CONTINUE
  /* After successfull insert make imsgo_notification_master.notification_sent = 0 */
  /* update "imsgo_notification_master"
set "notification_sent" = 1
where "notification_sent" = 0;
if sqlcode < 0 then
rollback work;
return '-4'
end if;*/
  //ELSE CONTINUE
  //commit work; //remove to use inside trigger
  return '1'
end

 End If;


//------------------------------------------------------------END---------------------------------------------------------




//---------------------------------------------------------START------------------------------------------------------------

If exists (select 1 from sysobjects where name ='sp_imsgo_notification_push' and Type='P') then
  Drop PROCEDURE sp_imsgo_notification_push;
ELSE

CREATE PROCEDURE "DBA"."sp_imsgo_notification_push"() //  ----  END : Error trace and Log update  ----
//  ******* START : Statement  [Ver:, VerDBId:, DB change date: ] *******
///////////////////////////////////////////////////////////////////////////////////
// Copyright © Meditab Software Inc. 2014
//
// Event/Function:
//
// Arguments: None
//
// Returns: None
//
// Description:
//
// Version History
//     1.0          Nov 4, 2015          New Func          Udit Jindal           Purpose: Sending push notification in IMSGo
///////////////////////////////////////////////////////////////////////////////////
begin
  /*
* Now insert records into imsgo_pending_notifications.
* Update all records in imsgo_notification_master.notification_sent = 1.
*/
  insert into "imsgo_pending_notifications"( "device_push_id","device_type","doctor_id","value","notification_type","action_type","action_value" ) on existing skip(
    select "dm"."device_push_id","dm"."device_type","nm"."doctor_id","count"(1),"nm"."tran_id_type","nm"."action_type","nm"."action_value"
      from "imsgo_notification_master" as "nm"
        left outer join "emp_master" on "emp_master"."empid" = "nm"."doctor_id"
        left outer join "security_users" as "su" on "emp_master"."user_name" = "su"."user_name"
        left outer join "imsgo_device_mst" as "dm" on "dm"."device_no" = "su"."imsgo_device_no"
      where "nm"."notification_sent" = 0
      group by "nm"."doctor_id","dm"."device_push_id","dm"."device_type","nm"."tran_id_type","nm"."action_type","nm"."action_value");
  if sqlcode < 0 then
    rollback work;
    return '-1'
  end if;
  //ELSE CONTINUE
  /* After successfull insert make imsgo_notification_master.notification_sent = 0 */
  update "imsgo_notification_master"
    set "notification_sent" = 1
    where "notification_sent" = 0;
  if sqlcode < 0 then
    rollback work;
    return '-2'
  end if;
  commit work;
  select "id" as "notification_id","device_push_id","device_type","doctor_id","value" as "count","notification_type","action_type","action_value"
    from "imsgo_pending_notifications"
end
 End If;


//------------------------------------------------------------END---------------------------------------------------------




//---------------------------------------------------------START------------------------------------------------------------

If Not Exists (SELECT 1 FROM sysobjects WHERE name ='imsgo_notification_master' AND Type='U') Then

CREATE TABLE "DBA"."imsgo_notification_master" (
	"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	"tran_id" INTEGER NOT NULL,
	"tran_id_type" INTEGER NOT NULL,
	"doctor_id" INTEGER NOT NULL,
	"created_date" TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP,
	"notification_sent" INTEGER NOT NULL,
	"action_type" INTEGER NULL,
	"action_value" VARCHAR(200) NULL,
	CONSTRAINT "tran_id-tran_id_type-doctor_id" PRIMARY KEY CLUSTERED ( "tran_id" ASC, "tran_id_type" ASC, "doctor_id" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."imsgo_notification_master" IS 'This table will get data from master tables like patient_checkin, schedule_master i.e for which we need to send notification to user. ';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."id" IS 'primary key';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."tran_id" IS 'tran_id of the table';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."tran_id_type" IS '1 -> schedule_detail, 2 -> patient_checkin';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."notification_sent" IS '1 -> Yes,  0 -> No ';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."action_type" IS '1 -> insert, 2 -> update, 3 -> delete';
COMMENT ON COLUMN "DBA"."imsgo_notification_master"."action_value" IS 'what changed.';



End If

//------------------------------------------------------------END---------------------------------------------------------



//---------------------------------------------------------START------------------------------------------------------------

If Not Exists (SELECT 1 FROM sysobjects WHERE name ='imsgo_pending_notifications' AND Type='U') Then

CREATE TABLE "DBA"."imsgo_pending_notifications" (
	"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	"device_push_id" VARCHAR(200) NULL,
	"device_type" VARCHAR(50) NULL,
	"doctor_id" INTEGER NOT NULL,
	"value" VARCHAR(500) NOT NULL,
	"notification_type" INTEGER NOT NULL,
	"action_type" INTEGER NULL,
	"action_value" VARCHAR(200) NULL,
	CONSTRAINT "doctor_id-notification_type" PRIMARY KEY ( "doctor_id" ASC, "notification_type" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."imsgo_pending_notifications" IS 'This table wil contain final notifications to be pushed';
COMMENT ON COLUMN "DBA"."imsgo_pending_notifications"."id" IS 'primary key';
COMMENT ON COLUMN "DBA"."imsgo_pending_notifications"."notification_type" IS '1 -> schedule_detail, 2 -> patient_checkin. Same as imsgo_notification_master.tran_id_type ';
COMMENT ON COLUMN "DBA"."imsgo_pending_notifications"."action_type" IS '1 -> insert, 2-> update, 3 -> delete';
COMMENT ON COLUMN "DBA"."imsgo_pending_notifications"."action_value" IS 'modified value';


End If

//------------------------------------------------------------END---------------------------------------------------------